package com.kaikeba.dao.imp;

import com.kaikeba.bean.Expressman;
import com.kaikeba.dao.BaseExpressmanDao;
import com.kaikeba.util.DruidUtil;
import com.mysql.cj.protocol.Resultset;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressmanDaoMysql implements BaseExpressmanDao {
    private static final String SQL_CONSOLE = "SELECT COUNT(ID) SUM, COUNT(TO_DAYS(REGISTERTIME)=TO_DAYS(NOW()) OR NULL) DAY FROM EXPRESSMAN";
    private static final String SQL_INSERT = "INSERT INTO EXPRESSMAN(ID, NAME, PHONE, IDNUMBER, PASSWORD, DISPATCHNUM, REGISTERTIME, LASTLOGINTIME) VALUES(?, ?, ?, ?, ?, 0, NOW(), null)";
    private static final String SQL_DELETE = "DELETE FROM EXPRESSMAN WHERE ID=?";
    private static final String SQL_DELETE_BY_PHONE = "DELETE FROM EXPRESSMAN WHERE PHONE=?";
    private static final String SQL_UPDATE = "UPDATE EXPRESSMAN SET NAME=?, PHONE=?, IDNUMBER=?, PASSWORD=? WHERE PHONE=?";
    private static final String SQL_FIND_ALL = "SELECT ID, NAME, PHONE, IDNUMBER, PASSWORD, DISPATCHNUM, REGISTERTIME, LASTLOGINTIME FROM EXPRESSMAN";
    private static final String SQL_FIND_LIMIT = "SELECT ID, NAME, PHONE, IDNUMBER, PASSWORD, DISPATCHNUM, REGISTERTIME, LASTLOGINTIME FROM EXPRESSMAN LIMIT ?, ?";
    private static final String SQL_FIND_BY_PHONE = "SELECT ID, NAME, PHONE, IDNUMBER, PASSWORD, DISPATCHNUM, REGISTERTIME, LASTLOGINTIME FROM EXPRESSMAN WHERE PHONE=?";

    @Override
    public Map<String, Integer> console() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            rs = state.executeQuery();
            Map<String, Integer> result = new HashMap<>();
            if(rs.next()){
                result.put("sum", rs.getInt("SUM"));
                result.put("day", rs.getInt("DAY"));
                return result;
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, rs);
        }
        return null;
    }

    @Override
    public boolean insert(Expressman man) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            state.setString(1, man.getId());
            state.setString(2, man.getName());
            state.setString(3, man.getPhone());
            state.setString(4, man.getIdnumber());
            state.setString(5, man.getPassword());
            int result = state.executeUpdate();
            if(result > 0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return false;
    }

    @Override
    public boolean delete(String id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setString(1, id);
            int result = state.executeUpdate();
            if(result > 0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return false;
    }

    @Override
    public boolean deleteByPhone(String phone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE_BY_PHONE);
            state.setString(1, phone);
            int result = state.executeUpdate();
            if(result > 0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return false;
    }

    @Override
    public boolean updateByPhone(String phone, Expressman man) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1, man.getName());
            state.setString(2, man.getPhone());
            state.setString(3, man.getIdnumber());
            state.setString(4, man.getPassword());
            state.setString(5, phone);
            int result = state.executeUpdate();
            if(result > 0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return false;
    }

    @Override
    public List<Expressman> findAll(int limit, int offset, int pageNumber) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        List<Expressman> list = new ArrayList<>();
        try {
            if(limit == 0){
                state = conn.prepareStatement(SQL_FIND_ALL);
            }else{
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                state.setInt(1, offset);
                state.setInt(2, pageNumber);
            }
            rs = state.executeQuery();
            while(rs.next()){
                Expressman man = new Expressman();
                man.setId(rs.getString("ID"));
                man.setName(rs.getString("NAME"));
                man.setPhone(rs.getString("PHONE"));
                man.setIdnumber(rs.getString("IDNUMBER"));
                man.setPassword(rs.getString("PASSWORD"));
                man.setDispatchNum(rs.getInt("DISPATCHNUM"));
                man.setRegisterTime(rs.getTimestamp("REGISTERTIME"));
                man.setLastLoginTime(rs.getTimestamp("LASTLOGINTIME"));
                list.add(man);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return null;
    }

    @Override
    public Expressman findByPhone(String phone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            state.setString(1, phone);
            rs = state.executeQuery();
            if(rs.next()){
                Expressman man = new Expressman();
                man.setId(rs.getString("ID"));
                man.setName(rs.getString("NAME"));
                man.setPhone(rs.getString("PHONE"));
                man.setIdnumber(rs.getString("IDNUMBER"));
                man.setPassword(rs.getString("PASSWORD"));
                man.setDispatchNum(rs.getInt("DISPATCHNUM"));
                man.setRegisterTime(rs.getTimestamp("REGISTERTIME"));
                man.setLastLoginTime(rs.getTimestamp("LASTLOGINTIME"));
                return man;
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return null;
    }
}
